package dao.impl;

import dao.MovieDao;
import entity.Movie;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class MovieDaoImpl extends BaseDao implements MovieDao {
    @Override
    public List<Movie> getMovieByPage(int pageNo, int pageSize) {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        List<Movie> list=new ArrayList<>();
        try{
            //1、连接
            conn=this.getConnection();
            //2、写sql
            String  sql="SELECT movieId,movieName,movieType,price,recommend,startTime," +
                    " endTime,votes FROM `movie` ORDER BY votes desc LIMIT ?,? ";
            //3、创建ps
            ps=conn.prepareStatement(sql);
            //4、设置参数
            ps.setInt(1,(pageNo-1)*pageSize);
            ps.setInt(2,pageSize);
            //5、执行sql
            rs=ps.executeQuery();
            //6、处理结果
            while (rs.next()){
                Movie movie=new Movie();
                movie.setMovieId(rs.getInt("movieId"));
                movie.setMovieName(rs.getString("movieName"));
                movie.setMovieType(rs.getString("movieType"));
                movie.setPrice(rs.getFloat("price"));
                movie.setRecommend(rs.getInt("recommend"));
                movie.setStartTime(rs.getString("startTime"));
                movie.setEndTime(rs.getString("endTime"));
                movie.setVotes(rs.getInt("votes"));
                list.add(movie);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            this.closeAll(conn,ps,rs);
        }
        return list;
    }

    @Override
    public Integer count() {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        Integer count=0;
        try{
            //1、连接
            conn=this.getConnection();
            //2、写sql
            String  sql="SELECT count(*) FROM `movie`";
            //3、创建ps
            ps=conn.prepareStatement(sql);
            //4、设置参数
            //5、执行sql
            rs=ps.executeQuery();
            //6、处理结果
            if(rs.next()){
                count=rs.getInt(1);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            this.closeAll(conn,ps,rs);
        }
        return count;
    }

    @Override
    public Integer countByName(String name) {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        Integer count=0;
        try{
            //1、连接
            conn=this.getConnection();
            //2、写sql
            String  sql="SELECT count(*) FROM `movie` where movieName=?";
            //3、创建ps
            ps=conn.prepareStatement(sql);
            //4、设置参数
            ps.setString(1,name);
            //5、执行sql
            rs=ps.executeQuery();
            //6、处理结果
            if(rs.next()){
                count=rs.getInt(1);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            this.closeAll(conn,ps,rs);
        }
        return count;
    }

    @Override
    public Integer insert(Movie movie) {
        Connection conn=null;
        PreparedStatement ps=null;
        Integer count=0;
        try{
            //1、连接
            conn=this.getConnection();
            //2、写sql
            String  sql="INSERT INTO `movie` (`movieName`, `movieType`, `price`, `recommend`, " +
                    " `startTime`, `endTime`, `votes`) VALUES (?,?,?,?,?,?,?)";
            //3、创建ps
            ps=conn.prepareStatement(sql);
            //4、设置参数
            ps.setString(1,movie.getMovieName());
            ps.setString(2,movie.getMovieType());
            ps.setFloat(3,movie.getPrice());
            ps.setInt(4,movie.getRecommend());
            ps.setString(5,movie.getStartTime());
            ps.setString(6,movie.getEndTime());
            ps.setInt(7,movie.getVotes());
            //5、执行sql
            count=ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            this.closeAll(conn,ps,null);
        }
        return count;
    }

    @Override
    public Movie getMovieById(Integer id) {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        Movie movie=null;
        try{
            //1、连接
            conn=this.getConnection();
            //2、写sql
            String  sql="SELECT movieId,movieName,movieType,price,recommend,startTime," +
                    " endTime,votes FROM `movie` where movieId=?";
            //3、创建ps
            ps=conn.prepareStatement(sql);
            //4、设置参数
            ps.setInt(1,id);
            //5、执行sql
            rs=ps.executeQuery();
            //6、处理结果
            if (rs.next()){
                movie=new Movie();
                movie.setMovieId(rs.getInt("movieId"));
                movie.setMovieName(rs.getString("movieName"));
                movie.setMovieType(rs.getString("movieType"));
                movie.setPrice(rs.getFloat("price"));
                movie.setRecommend(rs.getInt("recommend"));
                movie.setStartTime(rs.getString("startTime"));
                movie.setEndTime(rs.getString("endTime"));
                movie.setVotes(rs.getInt("votes"));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            this.closeAll(conn,ps,rs);
        }
        return movie;
    }

    @Override
    public Integer upate(Movie movie) {
        Connection conn=null;
        PreparedStatement ps=null;
        Integer count=0;
        try{
            //1、连接
            conn=this.getConnection();
            //2、写sql
            String  sql="UPDATE `movie` SET `movieName`=?, `movieType`=?, `price`=?, `recommend`=?," +
                    "  `startTime`=?, `endTime`=?, `votes`=? WHERE (`movieId`=?)";
            //3、创建ps
            ps=conn.prepareStatement(sql);
            //4、设置参数
            ps.setString(1,movie.getMovieName());
            ps.setString(2,movie.getMovieType());
            ps.setFloat(3,movie.getPrice());
            ps.setInt(4,movie.getRecommend());
            ps.setString(5,movie.getStartTime());
            ps.setString(6,movie.getEndTime());
            ps.setInt(7,movie.getVotes());
            ps.setInt(8,movie.getMovieId());
            //5、执行sql
            count=ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            this.closeAll(conn,ps,null);
        }
        return count;
    }

    @Override
    public Integer delete(Integer id) {
        Connection conn=null;
        PreparedStatement ps=null;
        Integer count=0;
        try{
            //1、连接
            conn=this.getConnection();
            //2、写sql
            String  sql="delete from `movie` WHERE (`movieId`=?)";
            //3、创建ps
            ps=conn.prepareStatement(sql);
            //4、设置参数
            ps.setInt(1, id);
            //5、执行sql
            count=ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            this.closeAll(conn,ps,null);
        }
        return count;
    }
}
